\set ON_ERROR_STOP

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
	'$libdir/plpgsql' LANGUAGE C;
	
CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
	'$libdir/plpgsql' LANGUAGE C;
	
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
	HANDLER plpgsql_call_handler
	VALIDATOR plpgsql_validator;


create table users (
	 username			varchar(30)		primary key,
	 pwhash			    varchar(30),
	 full_name		    varchar(255),
	 may_login		    boolean			default true,
	 may_alter_users	boolean			default false,
	 may_view_all		boolean			default false,
	 may_alter_all	    boolean			default false );

grant all on users to group phptasks;

create table topics (
	 id				    serial			primary key,
	 name				varchar(255)	not null,
	 default_user		varchar(30)		);

grant all on topics to group phptasks;
grant all on topics_id_seq to group phptasks;

create table priorities (
     n                  int             primary key,
     txt                varchar(30)     not null );
     
grant all on priorities to group phptasks;

insert into priorities (n, txt) values ( 1, 'Critical' );
insert into priorities (n, txt) values ( 2, 'High' );
insert into priorities (n, txt) values ( 3, 'ASAP' );
insert into priorities (n, txt) values ( 4, 'Low' );
insert into priorities (n, txt) values ( 5, 'Very Low' );

create table tasks (
	 id				    serial	    	primary key,
	 parent_id		    int4		    references tasks
									        on update cascade
					                        on delete cascade,
	 status			    varchar(30),
	 priority           int             default 3,
	 requestor		    varchar(255),
	 assigned_to	    varchar(30),
	 assigned_by	    varchar(30),
	 title		        varchar(255),
	 txt		        text,
	 created		    timestamp	    default now(),
	 modified		    timestamp	    default now() );
	 
grant all on tasks to group phptasks;
grant all on tasks_id_seq to group phptasks;

create rule tasks_insert_notify as on insert to tasks
    do notify taskinsert;

create rule tasks_update_notify as on update to tasks
    do notify taskupdate;

create function tasks_update_stamp() returns trigger as $task_stamp$
begin
    new.modified := current_timestamp;
    return new;
end;
$task_stamp$ language plpgsql;

create trigger task_stamp before update on tasks 
    for each row execute procedure tasks_update_stamp();

create table task_log (
     id                 serial          primary key,
	 task				int4		    references tasks
						                    on update cascade
					                        on delete cascade,
	 author			    varchar(30),
	 txt		        text,
	 created		    timestamp	    default now() );
	 
grant all on task_log to group phptasks;	 
grant all on task_log_id_seq to group phptasks;

create or replace rule tasks_assign_change as on update to tasks
	 where new.assigned_to <> old.assigned_to
	 do insert into task_log (task, author, txt) values ( new.id, 'phpTasks',
	    'Task reassigned from ' || coalesce(old.assigned_to, 'nobody') ||
	    ' to ' || coalesce(new.assigned_to, 'nobody') || '.' );		 
	 
create table task_files (
	 id				serial              primary key,
	 task			int4		        references tasks
						                    on update cascade
					                        on delete cascade,
	 filename		varchar(255)        not null,
	 mimetype		varchar(255)        default 'application/unknown',
	 created		timestamp	        default now(),
	 dat			bytea               );	 
	 
grant all on task_files to group phptasks;
grant all on task_files_id_seq to group phptasks;

create or replace view task_list as
    select id, parent_id, status, priority, 
           coalesce(p.txt, 'None') as priority_txt, requestor, 
           assigned_to, assigned_by, title, created, modified
      from tasks as t left join priorities p on t.priority = p.n;
           
grant all on task_list to group phptasks;           